package com.haole.ecommercepay.common;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * @author haole
 */
@Service("stringToExcelUtils")
public class StringToExcel {

    /**
     * 微信交易账单导出excel
     * @param downloadData
     * @param type 交易_ALL、交易_REFUND、交易_SUCCESS、资金
     * @param queryDate 查询时间 如2020-05-06
     * @param response
     * @throws IOException
     */
    public void downloadBillData(String downloadData, String type, String queryDate, HttpServletResponse response) throws IOException {
        String fileName = "微信"+type+"账单_" + queryDate + ".xlsx";
        response.setContentType("application/octet-stream; charset=utf-8");
        // 设置下载文件名
        response.addHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8"));
        this.tradeExportExcel(downloadData, type, response.getOutputStream());

    }

    private Map<String, Object> tradeSplit(String result) {
        Map<String, Object> map = new HashMap<String, Object>();
        String headerInfo = result.substring(0, result.indexOf("`"));
        String tradeMsg = result.substring(result.indexOf("`"));
        String tradeInfo = tradeMsg.substring(0, tradeMsg.indexOf("总"));
        String totalMsg = tradeMsg.substring(tradeMsg.indexOf("总"));
        String totalHeader = totalMsg.substring(0, totalMsg.indexOf("`"));
        String totalInfo = totalMsg.substring(totalMsg.indexOf("`")).replace("`", "");
        map.put("headerInfo", headerInfo);
        map.put("tradeInfo", tradeInfo);
        map.put("totalHeader", totalHeader);
        map.put("totalInfo", totalInfo);
        return map;
    }

    private void tradeExportExcel(String data, String type, OutputStream out) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 遍历物料类型 一个map对象为一个sheet页
        Sheet sheet = null;
        // 创建sheet页
        sheet = workbook.createSheet("微信"+type+"账单统计");
        // 字体
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);// 字号
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
        // 头背景色
        XSSFCellStyle styleHeader = workbook.createCellStyle();
        styleHeader.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        styleHeader.setFont(font);
        // 创建标题
        Row row = sheet.createRow(0);
        row.setHeightInPoints(30);
        // 设置excel头
        Cell cell = row.createCell(1);
        cell.setCellValue("微信"+type+"账单统计" + " 导出日期： " + DateUtils.formatDate(new Date(), "yyyy-MM-dd HH:mm:ss"));
        cell.setCellStyle(styleHeader);
        if (StringUtils.isEmpty(data)) {
            workbook.write(out);
        }
        Map<String, Object> map = tradeSplit(data);
        // 创建标题
        row = sheet.createRow(1);
        row.setHeightInPoints(20);
        String[] headerInfo = map.get("headerInfo").toString().split(",");
        // 设置excel头
        if (null != headerInfo && headerInfo.length > 0) {
            for (int i = 0; i < headerInfo.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(headerInfo[i]);
                cell.setCellStyle(styleHeader);
            }

        }
        int len = 0;
        // 内容背景色
        XSSFCellStyle styleContent = workbook.createCellStyle();
        styleContent.setFont(font);
        XSSFDataFormat format = workbook.createDataFormat();
        styleContent.setDataFormat(format.getFormat("@"));
        int titleCount = 1;
        int rowNum = 2;
        String[] tradeArray = map.get("tradeInfo").toString().split("``");
        if (null != tradeArray && tradeArray.length > 0) {
            for (int j = 0; j < tradeArray.length; j++) {
                row = sheet.createRow(rowNum);
                String[] infoStringList = tradeArray[j].split(",");
                for (int k = 0; k < infoStringList.length; k++) {
                    String value = infoStringList[k].replace("`", "");
                    if (StringUtils.isEmpty(value)) {
                        value = "-";
                    }
                    cell = row.createCell(k);
                    cell.setCellValue(value);
                    cell.setCellStyle(styleContent);
                }
                cell = row.createCell(infoStringList.length);
                cell.setCellValue("-");
                cell.setCellStyle(styleContent);
                rowNum = ++rowNum;
                titleCount++;
            }
        }
        rowNum = ++rowNum;
        String[] totalHeader = map.get("totalHeader").toString().split(",");
        row = sheet.createRow(rowNum);
        for (int g = 0; g < totalHeader.length; g++) {
            cell = row.createCell(g);
            cell.setCellValue(totalHeader[g]);
            cell.setCellStyle(styleContent);

        }
        rowNum = ++rowNum;
        row = sheet.createRow(rowNum);
        String[] totalInfo = map.get("totalInfo").toString().split(",");
        for (int g = 0; g < totalInfo.length; g++) {
            cell = row.createCell(g);
            cell.setCellValue(totalInfo[g]);
            cell.setCellStyle(styleContent);
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 微信资金账单导出excel
     * @param downloadData
     * @param queryDate
     * @param response
     * @throws IOException
     */
    public void downloadBillData(String downloadData, String queryDate, HttpServletResponse response) throws IOException {
        String fileName = "微信资金账单_" + queryDate + ".xlsx";
        response.setContentType("application/octet-stream; charset=utf-8");
        // 设置下载文件名
        response.addHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8"));
        this.fundFlowExportExcel(downloadData, response.getOutputStream());

    }

    private Map<String, Object> fundFlowSplit(String result) {
        Map<String, Object> map = new HashMap<String, Object>();
        String headerInfo = result.substring(0, result.indexOf("`"));
        String fundFlowMsg = result.substring(result.indexOf("`"));
        String fundFlowInfo = fundFlowMsg.substring(0, fundFlowMsg.indexOf("资"));
        String totalMsg = fundFlowMsg.substring(fundFlowMsg.indexOf("资"));
        String totalHeader = totalMsg.substring(0, totalMsg.indexOf("`"));
        String totalInfo = totalMsg.substring(totalMsg.indexOf("`")).replace("`", "");
        map.put("headerInfo", headerInfo);
        map.put("fundFlowInfo", fundFlowInfo);
        map.put("totalHeader", totalHeader);
        map.put("totalInfo", totalInfo);
        return map;
    }

    private void fundFlowExportExcel(String data, OutputStream out) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 遍历物料类型 一个map对象为一个sheet页
        Sheet sheet = null;
        // 创建sheet页
        sheet = workbook.createSheet("微信资金账单统计");
        // 字体
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);// 字号
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
        // 头背景色
        XSSFCellStyle styleHeader = workbook.createCellStyle();
        styleHeader.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        styleHeader.setFont(font);
        // 创建标题
        Row row = sheet.createRow(0);
        row.setHeightInPoints(30);
        // 设置excel头
        Cell cell = row.createCell(1);
        cell.setCellValue("微信资金账单统计" + " 导出日期： " + DateUtils.formatDate(new Date(), "yyyy-MM-dd HH:mm:ss"));
        cell.setCellStyle(styleHeader);
        if (StringUtils.isEmpty(data)) {
            workbook.write(out);
        }
        Map<String, Object> map = fundFlowSplit(data);
        // 创建标题
        row = sheet.createRow(1);
        row.setHeightInPoints(20);
        String[] headerInfo = map.get("headerInfo").toString().split(",");
        // 设置excel头
        if (null != headerInfo && headerInfo.length > 0) {
            for (int i = 0; i < headerInfo.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(headerInfo[i]);
                cell.setCellStyle(styleHeader);
            }

        }
        int len = 0;
        // 内容背景色
        XSSFCellStyle styleContent = workbook.createCellStyle();
        styleContent.setFont(font);
        XSSFDataFormat format = workbook.createDataFormat();
        styleContent.setDataFormat(format.getFormat("@"));
        int titleCount = 1;
        int rowNum = 2;
        if (map.get("fundFlowInfo").toString().contains("&")) {
            String[] fundFlowArray = map.get("fundFlowInfo").toString().split("*");
            if (null != fundFlowArray && fundFlowArray.length > 0) {
                for (int j = 0; j < fundFlowArray.length; j++) {
                    row = sheet.createRow(rowNum);
                    String[] infoStringList = fundFlowArray[j].split(",");
                    for (int k = 0; k < infoStringList.length; k++) {
                        String value = infoStringList[k].replace("`", "");
                        if (StringUtils.isEmpty(value)) {
                            value = "-";
                        }
                        cell = row.createCell(k);
                        cell.setCellValue(value);
                        cell.setCellStyle(styleContent);
                    }
                    rowNum = ++rowNum;
                    titleCount++;
                }
            }
        }
        rowNum = ++rowNum;
        String[] totalHeader = map.get("totalHeader").toString().split(",");
        row = sheet.createRow(rowNum);
        for (int g = 0; g < totalHeader.length; g++) {
            cell = row.createCell(g);
            cell.setCellValue(totalHeader[g]);
            cell.setCellStyle(styleContent);

        }
        rowNum = ++rowNum;
        row = sheet.createRow(rowNum);
        String[] totalInfo = map.get("totalInfo").toString().split(",");
        for (int g = 0; g < totalInfo.length; g++) {
            cell = row.createCell(g);
            cell.setCellValue(totalInfo[g]);
            cell.setCellStyle(styleContent);
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
